﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Backend;
using System.Data.SqlClient;

namespace PersistenceTier
{
    public class FlightPersistance
    {

        SqlConnection conn;

        public FlightPersistance()
        {
            conn = DBConnection.getConnection().getSQLConnection();

        }


       // method from the first version of the software- not used currently 
        public List<Leg> flightSearch(Airport departAirp, Airport arrivalAirp, String date)
        {

            conn.Open();
            // the query returns all possible 2 leg routs
            //old one: string Query ="use dm77_1 select * From (select * From (select * From Flight where depAirport='SOF' and depTime>'"+date+"') as Departures where Departures.arrAirport in (select Flight.depAirport From Flight where arrAirport='AAL')) as firstLeg,(select * from Flight Where depAirport in (select arrAirport from Flight where depAirport='SOF') and arrAirport='AAL') as secondLeg Where firstLeg.arrAirport=secondLeg.depAirport and firstLeg.arrTime<secondLeg.depTime";
            //string Query = "use dm77_1 select * From (select * From (select * From Flight where depAirport='" + departAirp.Id + "'and depTime>'" + date + "') as Departures where Departures.arrAirport in (select Flight.depAirport From Flight where arrAirport='" + arrivalAirp.Id + "')) as firstLeg,(select * from Flight Where depAirport in (select arrAirport from Flight where depAirport='" + departAirp.Id + "') and arrAirport='" + arrivalAirp.Id + "') as secondLeg Where firstLeg.arrAirport=secondLeg.depAirport and firstLeg.arrTime<secondLeg.depTime";
            string Query = "select* from Flight";
            Console.WriteLine(Query);
            Console.WriteLine();

      


            List<Leg> legs = new List<Leg>();

            try
            {
                SqlDataReader reader = null;

                SqlCommand myCommand = new SqlCommand(Query, conn);
                ////SqlCommand myCommand2 = new SqlCommand(Query2, conn);

                ////reader = myCommand2.ExecuteReader();
            
                ////flights = buildDirectFlights(reader);
                ////reader.Close();
                reader = myCommand.ExecuteReader();
                //buildFlights(flights, reader);

                legs = buildAllLegs(reader);



            }

            catch (Exception e)
            {
                Console.WriteLine("fail boss");
                Console.WriteLine(e.Message);
            }

            conn.Close();
            return legs;
        }


        private List<Leg> buildAllLegs(SqlDataReader reader)
        {
            List<Leg> legs = new List<Leg>();

            while (reader.Read())
            {
             
                Leg legObj = new Leg();

                legObj.Id = (int)reader[0];
                legObj.DepartureAirp = new Airport(reader[1].ToString());
                legObj.ArrivalAirp = new Airport(reader[2].ToString());
                legObj.DepartureTime = reader[3].ToString();
                legObj.ArrivalTime = reader[4].ToString();
                legObj.NumberOfSeats = (int)reader[5];
                legObj.Airline = reader[6].ToString();
                legObj.Aircraft = reader[7].ToString();
                legObj.Price = Convert.ToDouble(reader[8]);

             legs.Add(legObj);
            }
                //add the flight obj to the list of flights
                
                return legs;

        }

        //оld method currently not in use 
        private List<Flight> buildDirectFlights(SqlDataReader reader)
        {
            List<Flight> flights = new List<Flight>();

            while (reader.Read())
            {
                Flight flightObj = new Flight();
                Leg legObj = new Leg();

                legObj.Id = (int)reader[0];
                legObj.DepartureAirp = new Airport(reader[1].ToString());
                legObj.ArrivalAirp = new Airport(reader[2].ToString());
                legObj.DepartureTime = reader[3].ToString();
                legObj.ArrivalTime = reader[4].ToString();
                legObj.NumberOfSeats = (int)reader[5];
                legObj.Airline = reader[6].ToString();
                legObj.Aircraft = reader[7].ToString();
                legObj.Price = Convert.ToDouble(reader[8]);

                //add the direct leg to the flight obj
                flightObj.AddLeg(legObj);

                //add the flight obj to the list of flights
                flights.Add(flightObj);

            }

            return flights;
        }

        //оld method currently not in use 
        private List<Flight> buildFlights(List<Flight> flights, SqlDataReader reader)
        {

            while (reader.Read())
            {
                Flight flightObj = new Flight();
                Leg legObj = new Leg();

                //getting info about the 1st leg
                legObj.Id = (int)reader[0];
                legObj.DepartureAirp = new Airport(reader[1].ToString());
                legObj.ArrivalAirp = new Airport(reader[2].ToString());
                legObj.DepartureTime = reader[3].ToString();
                legObj.ArrivalTime = reader[4].ToString();
                legObj.NumberOfSeats = (int)reader[5];
                legObj.Airline = reader[6].ToString();
                legObj.Aircraft = reader[7].ToString();
                legObj.Price = Convert.ToDouble(reader[8]);

                //add the 1st leg to the flight obj
                flightObj.AddLeg(legObj);

                legObj = new Leg();

                //getting info about the 2nd leg
                legObj.Id = (int)reader[9];
                legObj.DepartureAirp = new Airport(reader[10].ToString());
                legObj.ArrivalAirp = new Airport(reader[11].ToString());
                legObj.DepartureTime = reader[12].ToString();
                legObj.ArrivalTime = reader[13].ToString();
                legObj.NumberOfSeats = (int)reader[14];
                legObj.Airline = reader[15].ToString();
                legObj.Aircraft = reader[16].ToString();
                legObj.Price = Convert.ToDouble(reader[17]);

                flightObj.AddLeg(legObj);

                //add the flight obj to the list of flights
                flights.Add(flightObj);



            }



            return flights;
        }












        public List<Leg> searchFlightsFrom(Airport airport,String date,int numberOfPassengers)
        {
            //open connection
            conn.Open();

            string Query = "select* from flight where depAirport='" + airport.Id + "' and depTime>'" + date + " 00:00:00.000' and depTime<'"+date + " 23:59:59.000' and nrSeats >= '"+numberOfPassengers + "'";
            Console.WriteLine(Query);
            Console.WriteLine();

            List<Leg> legs = new List<Leg>();

          
            try
            {
                SqlDataReader reader = null;

                SqlCommand myCommand = new SqlCommand(Query, conn);
                reader = myCommand.ExecuteReader();
                legs = buildAllLegs(reader);



            }

            catch (Exception e)
            {
                Console.WriteLine("fail boss");
                Console.WriteLine(e.Message);
            }

            conn.Close();
            return legs;
        }


        //оld method currently not in use 
        public List<Leg> searchDirectFlights(Airport depAirp, Airport arAirp, string date)
        {
            conn.Open();
            // the query returns all possible 2 leg routs
            //old one: string Query ="use dm77_1 select * From (select * From (select * From Flight where depAirport='SOF' and depTime>'"+date+"') as Departures where Departures.arrAirport in (select Flight.depAirport From Flight where arrAirport='AAL')) as firstLeg,(select * from Flight Where depAirport in (select arrAirport from Flight where depAirport='SOF') and arrAirport='AAL') as secondLeg Where firstLeg.arrAirport=secondLeg.depAirport and firstLeg.arrTime<secondLeg.depTime";
            //string Query = "use dm77_1 select * From (select * From (select * From Flight where depAirport='" + departAirp.Id + "'and depTime>'" + date + "') as Departures where Departures.arrAirport in (select Flight.depAirport From Flight where arrAirport='" + arrivalAirp.Id + "')) as firstLeg,(select * from Flight Where depAirport in (select arrAirport from Flight where depAirport='" + departAirp.Id + "') and arrAirport='" + arrivalAirp.Id + "') as secondLeg Where firstLeg.arrAirport=secondLeg.depAirport and firstLeg.arrTime<secondLeg.depTime";
            string Query = "use dm77_1 select* from Flight where depAirport='" + depAirp.Id + "' and arrAirport='" + arAirp .Id+ "' and depTime>'" + date + " 00:00:00.000' and depTime<'"+date + " 23:59:59.000'";
            Console.WriteLine(Query);
            Console.WriteLine();

            List<Leg> legs = new List<Leg>();

            try
            {
                SqlDataReader reader = null;

                SqlCommand myCommand = new SqlCommand(Query, conn);
                ////SqlCommand myCommand2 = new SqlCommand(Query2, conn);

                ////reader = myCommand2.ExecuteReader();

                ////flights = buildDirectFlights(reader);
                ////reader.Close();
                reader = myCommand.ExecuteReader();
                //buildFlights(flights, reader);

                legs = buildAllLegs(reader);



            }

            catch (Exception e)
            {
                Console.WriteLine("fail boss");
                Console.WriteLine(e.Message);
            }

            conn.Close();
            return legs;
        }
    }
}
